package br.com.fiap.trabalho.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import br.com.fiap.trabalho.entity.Actor;
import br.com.fiap.trabalho.entity.Category;
import br.com.fiap.trabalho.entity.Movie;
import br.com.fiap.trabalho.entity.Studio;

public class JDBCMovieDAO implements MovieDAO{

	private Connection cn;
	private PreparedStatement st;
	
	private boolean openConnection() throws Exception{
		try{
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); 
			String url = "jdbc:derby:simpleDB;create=true";
			cn = DriverManager.getConnection(url,"TrabalhoPersistenciaJava","TrabalhoPersistenciaJava");
			return true;
		}
		catch(Exception ex){
			throw ex;
		}
	}
	
	// 40 min
	public Movie createMovie(Movie movie) {
		
		try {
			openConnection();
			cn.setAutoCommit(false);
			if (movie.getStudio() == null || movie.getActor() == null || movie.getActor().isEmpty() 
					|| movie.getCategory() == null || movie.getCategory().isEmpty()){
				return null;
			}
			if (!verifyStudio(movie)){
				return null;
			}
			
			st = cn.prepareStatement("INSERT INTO MOVIE (TITLE, ANO, ID_STUDIO) VALUES (?,?,?)", 
					Statement.RETURN_GENERATED_KEYS);
			st.setString(1, movie.getTitle());
			st.setInt(2, movie.getYear());
			st.setInt(3,movie.getStudio().getId());
			st.executeUpdate();
			
			movie.setId(retornaIdGerado());
			if (associateActors(movie) && associateCategories(movie)){
				cn.commit();
			}
			else{
				cn.rollback();
				return null;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			try {
				cn.rollback();
				return null;
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		finally{
			try {
				cn.setAutoCommit(true);
				if (st != null){
					st.close();
				}
				cn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return movie;
	}

	//20 min
	public boolean deleteMovie(Movie movie) {
		boolean retorno = true;
		
		try {
			openConnection();
			cn.setAutoCommit(false);
			
			st = cn.prepareStatement("DELETE FROM MOVIE_ACTOR WHERE ID_MOVIE = ?");
			st.setInt(1, movie.getId());
			st.executeUpdate();
						
			st = cn.prepareStatement("DELETE FROM MOVIE_CATEGORY WHERE ID_MOVIE = ?");
			st.setInt(1, movie.getId());
			st.executeUpdate();
						
			st = cn.prepareStatement("DELETE FROM MOVIE WHERE ID_MOVIE = ?");
			st.setInt(1, movie.getId());
			st.executeUpdate();
			cn.commit();
			
			
		} catch (Exception e) {
			e.printStackTrace();
			try {
				cn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			retorno = false;
		}
		finally{
			try {
				cn.setAutoCommit(true);
				if (st != null){
					st.close();
				}
				cn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return retorno;
	}

	//20 min
	public List<Movie> selectMovieByTitle(String title) {
		
		try {
			openConnection();
			st = cn.prepareStatement("SELECT m.ID_MOVIE as idfilme, m.TITLE as titulofilme, m.ANO as anofilme " +
					"FROM MOVIE m WHERE m.TITLE = ? ");
			st.setString(1, title);
			ResultSet rs = st.executeQuery();
			
			return fillListMovies(rs);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	// 3 min
	public List<Movie> selectMovieByYear(int year) {
		try {
			openConnection();
			st = cn.prepareStatement("SELECT m.ID_MOVIE as idfilme, m.TITLE as titulofilme, m.ANO as anofilme " +
					"FROM MOVIE m WHERE m.ANO = ? ");
			st.setInt(1, year);
			ResultSet rs = st.executeQuery();
			
			return fillListMovies(rs);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	// 10 min
	public List<Movie> selectMoviesByActorName(String actorName) {
		try {
			openConnection();
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT DISTINCT m.ID_MOVIE as idfilme, m.TITLE as titulofilme, m.ANO as anofilme FROM MOVIE m ");
			sb.append("INNER JOIN MOVIE_ACTOR am ON m.ID_MOVIE = am.ID_MOVIE ");
			sb.append("INNER JOIN ACTOR a ON am.ID_ACTOR = a.ID_ACTOR ");
			sb.append("WHERE a.FULLNAME = ? ");
			String query = sb.toString();
			st = cn.prepareStatement(query);
			st.setString(1, actorName);
			ResultSet rs = st.executeQuery();
			
			return fillListMovies(rs);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	// 25 min
	public List<Movie> selectMovieByCategoryName(String categoryName) {
		try {
			openConnection();
			
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT DISTINCT m.ID_MOVIE as idfilme, m.TITLE as titulofilme, m.ANO as anofilme FROM MOVIE m ");
			sb.append("INNER JOIN MOVIE_CATEGORY cm ON m.ID_MOVIE = cm.ID_MOVIE ");
			sb.append("INNER JOIN CATEGORY c ON cm.ID_CATEGORY = c.ID_CATEGORY ");
			sb.append("WHERE c.NAME = ? ");
			String query = sb.toString();
			st = cn.prepareStatement(query);
			st.setString(1, categoryName);
			ResultSet rs = st.executeQuery();
	
			return fillListMovies(rs);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	// 10 min
	public List<Movie> selectMovieByStudioName(String studioName) {
		try {
			openConnection();
			String query = "SELECT DISTINCT m.ID_MOVIE as idfilme, m.TITLE as titulofilme, m.ANO as anofilme FROM MOVIE m "
					+ "INNER JOIN STUDIO s ON m.ID_STUDIO = s.ID_STUDIO "
					+ "WHERE s.NAME = ?";
					
			st = cn.prepareStatement(query);
			st.setString(1, studioName);
			ResultSet rs = st.executeQuery();
			
			return fillListMovies(rs);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	private boolean verifyStudio(Movie movie) throws SQLException {
		
		Studio studio = movie.getStudio();
		st = cn.prepareStatement("SELECT * FROM STUDIO WHERE ID_STUDIO = ?");
		st.setInt(1, studio.getId());
		ResultSet rs = st.executeQuery();
				
		return rs.next();
	}

	private boolean associateCategories(Movie movie) throws SQLException {
		boolean verifica = true;
		
		List<Category> listCategories = movie.getCategory();
		for (Category category : listCategories) {
			st = cn.prepareStatement("SELECT * FROM CATEGORY WHERE ID_CATEGORY = ?");
			st.setInt(1, category.getId());
			ResultSet rs = st.executeQuery();
			
			if (!rs.next()){
				return false;
			}
			
			st = cn.prepareStatement("INSERT INTO MOVIE_CATEGORY (ID_CATEGORY, ID_MOVIE) VALUES (?,?)");
			st.setInt(1, category.getId());
			st.setInt(2, movie.getId());
			st.executeUpdate();
		}
		
		return verifica;
	}

	private boolean associateActors(Movie movie) throws SQLException {
		boolean verifica = true;
		
		List<Actor> listActors = movie.getActor();
		for (Actor actor : listActors) {
			st = cn.prepareStatement("SELECT * FROM ACTOR WHERE ID_ACTOR = ?");
			st.setInt(1, actor.getId());
			ResultSet rs = st.executeQuery();
			
			if (!rs.next()){
				return false;
			}
			
			st = cn.prepareStatement("INSERT INTO MOVIE_ACTOR (ID_ACTOR, ID_MOVIE) VALUES (?,?)");
			st.setInt(1, actor.getId());
			st.setInt(2, movie.getId());
			st.executeUpdate();
		}
		return verifica;
	}

	private List<Movie> fillListMovies(ResultSet rs) throws SQLException {
		List<Movie> listMovies = new ArrayList<Movie>();
		while(rs.next()){
			Movie movie = new Movie();
			movie.setId(rs.getInt("idfilme"));
			movie.setTitle(rs.getString("titulofilme"));
			movie.setYear(rs.getInt("anofilme"));
			listMovies.add(movie);
		}
		return listMovies;
	}
	
	private int retornaIdGerado() throws SQLException{
		ResultSet rs = st.getGeneratedKeys();
		if(rs.next()) 
		{
			return rs.getInt(1);
		}
		
		return -1;
	}
	

}
